package com.mallcai.bigdata.ladon.datasource.task;

import com.mallcai.bigdata.ladon.datasource.DataVoExtractor;
import com.mallcai.bigdata.ladon.datasource.ResultSetDataTool;
import com.mallcai.bigdata.ladon.dpl.vo.DataType;
import com.mallcai.bigdata.ladon.service.system.IMailService;
import com.mallcai.bigdata.ladon.utils.CompressZipUtils;
import com.mallcai.bigdata.ladon.utils.DateUtils;
import com.mallcai.bigdata.ladon.vo.ExportParam;
import com.mallcai.bigdata.ladon.vo.Pair;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.util.IOUtils;

import java.io.*;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * described : 导出数据任务
 * Created by DiLong on 2017-12-20 16:07.
 * Modify:
 */
@Slf4j
public class ExportDataRunnable implements Runnable {

    private static DecimalFormat doubleFormat = new DecimalFormat("#0.00");

    private static final long NANO2MS = 1000 * 1000;

    private static final int    TASK_ERROR   = 0;
    private static final int    TASK_SUCCESS = 1;
    private static final String CHARSET      = "UTF-8";
    private static       byte[] bom          = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};

    private static final int    MAX_SIZE     = 70 * 10000; // 20w条
    private static final int    MAX_XLS_SIZE = 6 * 10000;  // 6万
    private static final String KEY_LIMIT    = " LIMIT ";

    private static final int MAX_TIME_OUT = 500; // 超时时间

    private Connection            conn;
    private ExportParam           query;
    private String                saveFileName;
    private Map<String, DataType> columnTypes;
    private IMailService          iMailService;


    public ExportDataRunnable(Connection conn, ExportParam query, IMailService iMailService) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        this.conn = conn;
        this.query = query;
        this.iMailService = iMailService;
        saveFileName = sdf.format(new Date()) + "_" + query.getTitle();
    }

    @Override
    public void run() {
        Pair<String, Map<Integer, Object>> dataSql = new Pair<>(query.getRawSql(), query.getParams());
        if (conn == null || dataSql == null) {
            return;
        }
        log.debug("----> 导出文件类型为: {}", query.getExportFileType());

        ResultSet rsData = null;
        Statement stmtData = null;
        File file = null;
        BufferedWriter writer = null;
        FileOutputStream outputStream = null;
        String suffix = StringUtils.lowerCase(query.getExportFileType());
        try {
            stmtData = getStatement(dataSql);
            if (stmtData == null) {
                return;
            }
            //执行sql
            long startTime = System.nanoTime();
            rsData = getResultSet(stmtData, dataSql.getLeft());
            long endTime = System.nanoTime();
            long waitTime = (endTime - startTime) / NANO2MS;
            if (waitTime > MAX_TIME_OUT) {
                log.warn("导出数据查询耗时:{}ms, 执行sql:{}", waitTime, stmtData.toString());
            }

            if (!StringUtils.equals(suffix, "xls") && !StringUtils.equals(suffix, "csv")) {
                suffix = "csv";
            }

            file = new File(saveFileName + "." + suffix);
            boolean createStatus = file.createNewFile();
            if (!createStatus) {
                log.error("创建文件失败:{}", file.getPath());
                return;
            }

            //获取列表对象
            ResultSetMetaData resultSetMetaData = rsData.getMetaData();
            String column;
            //获取查询结果字段信息
            final int size = resultSetMetaData.getColumnCount();
            List<String> columns = new ArrayList<>();

            columnTypes = new HashMap<>();
            for (int i = 1; i <= size; i++) {
                column = resultSetMetaData.getColumnLabel(i);

                DataType type = ResultSetDataTool.getDataType(resultSetMetaData.getColumnClassName(i));
                columnTypes.put(column, type);
                columns.add(column);
            }

            if ("xls".equals(suffix)) {
//                outputStream = new FileOutputStream(file);
//                SplitVo splitVo = splitRowToRows(rsData, columns);
//                xlsFileCreate(outputStream, splitVo);
            } else {
                writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), CHARSET), 1024);
                // 获取字段信息
                csvFileCreate(writer, columns, rsData);
            }
        } catch (Exception | Error e) {
            log.error("获取数据错误", e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
            } catch (Exception e) {
                log.error("关闭流失败!", e.getMessage());
            }
            try {
                if (writer != null) {
                    writer.flush();
                    writer.close();
                }
            } catch (Exception e) {
                log.error("关闭csv流失败!", e.getMessage());
            }
            try {
                if (rsData != null) {
                    rsData.close();
                }
            } catch (Exception e) {
                log.error("关闭rsData失败!", e.getMessage());
            }
            try {
                if (stmtData != null) {
                    stmtData.close();
                }
            } catch (Exception e) {
                log.error("关闭stmtData失败!", e.getMessage());
            }

            try {
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                log.error("关闭数据库连接失败!", e.getMessage());
            }
        }

        String zipFilePath = saveFileName + ".zip";
        //压缩csv文件
        try {
            log.debug("开始压缩文件~");
            CompressZipUtils.compressFile(saveFileName + "." + suffix, zipFilePath);
            log.debug("压缩文件结束, 原始文件大小:{}", file.getTotalSpace());
        } catch (Exception e) {
            log.error("压缩文件失败, sourceFile:{}.{}, targetFile:{}.zip", saveFileName, suffix, saveFileName, e);
        }

        try {
            if (file != null) {
                //开始发送邮件
                byte[] bytes = IOUtils.toByteArray(new FileInputStream(zipFilePath));
                Map<String, byte[]> outStreamMap = new HashMap<>();
                outStreamMap.put(zipFilePath, bytes);
                iMailService.sendCustomMailWithFrom(query.getFrom(),
                        query.getEmail(),
                        null,
                        null,
                        query.getTitle(),
                        query.getEmailHtmlContent(),
                        outStreamMap);

            }
        } catch (Exception e) {
            log.error("文件发送失败!", e);
        }

        //清除临时文件
        try {
            boolean status;
            if (file != null) {
                status = file.delete();
                log.debug("删除临时文件:{}, status:{}", file.getPath(), status);
            }
            File zipFile = new File(zipFilePath);
            status = zipFile.delete();
            log.debug("删除临时文件:{}, status:{}", zipFilePath, status);
        } catch (Exception e) {
            log.error("删除临时文件失败", e);
        }
    }

//    private void xlsFileCreate(FileOutputStream outputStream, SplitVo splitVo) throws Exception {
//        GenExcelFile excelFile = new GenExcelFile();
//        List<ComplexHead> header = splitVo.getColumns();
//        List<Map<String, Object>> rows = splitVo.getRows();
//        Map<String, String> fieldNameToShow = new HashMap<>(); //字段对应显示名称
//
//        log.debug("数据量为: {}", rows.size());
//
//        excelFile.addSheet("", query.getTitle(), query.getTips(), header, rows, columnTypes, fieldNameToShow);
//        HSSFWorkbook book = excelFile.getWorkbook();
//        book.write(outputStream);
//    }

    private void csvFileCreate(BufferedWriter writer, List<String> columns, ResultSet rsData) throws Exception {
        // 写入头信息
        writeHeads(writer, query.getTitle(), query.getTips(), columns, query.getFieldNameToShow());
        Map<String, DataType> displayDataTypes = query.getDisplayDataTypes();
        if(displayDataTypes == null){
            displayDataTypes = new HashMap<>();
        }
        //获取结果集
        Object columnValue;

        int totalSize = 0;
        while (rsData.next()) {
            totalSize++;
            Map<String, Object> row = new HashMap<>();
            for (String column : columns) {
                DataType dt = columnTypes.get(column);
                if (dt == null) {
                    continue;
                }
                columnValue = ResultSetDataTool.getValue(rsData, column, dt);
                row.put(column, DataVoExtractor.convertValue(columnValue, displayDataTypes.get(column), dt));
            }
            writeSpiltRow(writer, row, columns);
            if (totalSize > MAX_SIZE) {
                break;
            }
        }

        rsData.close(); //关闭数据集

        //已经超过最大的导出量了
        if (totalSize > MAX_SIZE) {
            Map<String, Object> overTips = new HashMap<>();
            overTips.put(columns.get(0), "您查询的数据已经超过最大导出量【" + MAX_SIZE + "】请试着缩小查询范围!");
            writeSpiltRow(writer, overTips, columns);
        }
    }

    /**
     * 写入头相关信息
     *
     * @throws IOException e
     */
    private void writeHeads(BufferedWriter csvWriter, String title, List<String> tips, List<String> afterSplitColumns,
                            Map<String, String> fieldToShowName) throws IOException {
        Map<String, Object> map = new HashMap<>();
        if (fieldToShowName == null) {
            fieldToShowName = new HashMap<>();
        }

        // BOM头,防止以Excel打开中文乱码
        csvWriter.write(new String(bom, "UTF-8"));
        /*写入标题, 居中显示*/
        int midIndex = afterSplitColumns.size() / 2;
        map.put(afterSplitColumns.get(midIndex), title);
        writeSpiltRow(csvWriter, map, afterSplitColumns);

        /*写入tips*/
        if (tips != null) {
            for (String tip : tips) {
                map.clear();
                map.put(afterSplitColumns.get(0), tip);
                writeSpiltRow(csvWriter, map, afterSplitColumns);
            }
        }

        /*写入表字段头*/
        StringBuilder sb = new StringBuilder();
        for (String col : afterSplitColumns) {
            String c = col;
            if (fieldToShowName.containsKey(col)) {
                c = fieldToShowName.get(col);
            }
            if (StringUtils.isBlank(c)) {
                c = col;
            }
            sb.append("\"").append(c).append("\",");
        }
        csvWriter.write(sb.toString());
        csvWriter.newLine();
    }

    /**
     * 填写已经分裂好的行数据
     *
     * @param csvWriter BufferedWriter
     * @param row       Map
     * @param columns   List 字段列表
     * @throws IOException e
     */
    private void writeSpiltRow(BufferedWriter csvWriter, Map<String, Object> row, List<String> columns) throws IOException {
        StringBuilder sb = new StringBuilder(columns.size() * 32);
        for (String c : columns) {
            Object value = row.get(c);
            if (value == null) {
                sb.append(",");
                continue;
            }

            String txtValue = String.valueOf(value);
            DataType dt = columnTypes.get(c);
            if (dt == null) {
                dt = DataType.STRING;
            }
            try {
                switch (dt) {
                    case BOOLEAN:
                        break;
                    case DATE:
                    case TIME:
                    case DATETIME:
                        sb.append("\"").append(value).append("\",");
                        break;
                    case BIG_DECIMAL:
                    case DOUBLE:
                    case FLOAT:
                    case LONG:
                        sb.append(doubleFormat.format(Double.parseDouble(txtValue))).append(",");
                        break;
                    case INTEGER:
                        sb.append(Integer.parseInt(txtValue)).append(",");
                        break;
                    case STRING:
                        sb.append("\"").append(txtValue).append("\",");
                        break;
                    case OBJECT:
                    default:
                        sb.append("\"").append(txtValue).append("\",");
                        break;
                }
            } catch (Exception e) {
                log.warn("类型转换错误! dt:{} msg:{}", dt.name(), e.getMessage());
                sb.append("\"").append(txtValue).append("\",");
            }
        }
        csvWriter.write(sb.toString());
        csvWriter.newLine();
    }

    /**
     * 判断字符串是否为数字
     *
     * @param str String
     * @return boolean
     */
    private boolean isDigit(String str) {
        str = StringUtils.trimToEmpty(str);
        if (StringUtils.isBlank(str)) {
            return false;
        }

        int len = str.length();
        for (int i = 0; i < len; i++) {
            if (!Character.isDigit(str.charAt(i))) {
                return false;
            }
        }
        return true;
    }

    private Statement getStatement(Pair<String, Map<Integer, Object>> dataSql) throws Exception {

        if (dataSql == null || dataSql.getLeft() == null) {
            return null;
        }
        try {
            PreparedStatement preStmt = conn.prepareStatement(addLimit(dataSql.getLeft()));
            //写入预编译时的参数
            if (dataSql.getRight() != null && dataSql.getRight().size() > 0) {
                Object obj;
                String objStr;
                for (Integer key : dataSql.getRight().keySet()) {
                    obj = dataSql.getRight().get(key);
                    objStr = String.valueOf(obj);
                    if (obj instanceof String) { //字符串
                        preStmt.setString(key, objStr);
                    } else if (obj instanceof Integer) { //整数类型
                        preStmt.setInt(key, Integer.parseInt(objStr));
                    } else if (obj instanceof Boolean) { //布尔类型
                        preStmt.setBoolean(key, Boolean.valueOf(objStr));
                    } else if (obj instanceof Double) {
                        preStmt.setDouble(key, Double.valueOf(objStr));
                    } else {
                        preStmt.setObject(key, obj);
                    }
                }
            }
            return preStmt;
        } catch (Exception e) {
            return conn.createStatement();
        }
    }

    private ResultSet getResultSet(Statement statement, String sql) throws SQLException {
        ResultSet rsData;
        //执行sql
        if (statement instanceof PreparedStatement) {
            PreparedStatement tmp = (PreparedStatement) statement;
            rsData = tmp.executeQuery();
        } else {
            rsData = statement.executeQuery(sql);
        }
        return rsData;
    }

    private String addLimit(String sql) {
        //特殊符号判断
        sql = StringUtils.trim(sql);
        if (StringUtils.endsWith(sql, ";")) {
            sql = StringUtils.substring(sql, 0, sql.length() - 1); //去除最后一个";"
        }

        //判断是否有LIMIT存在
        if (StringUtils.indexOf(StringUtils.upperCase(sql), KEY_LIMIT) <= 0) {
            sql = sql + KEY_LIMIT + (MAX_SIZE + 1); //这里多加一条用于判断是否还有更多的数据
        }
        return sql;
    }
}
